package com.gwtjs.icustom.ioutils;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
//import java.sql.PreparedStatement;
//import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.gwtjs.icustom.entity.DatabaseSchema;
import com.gwtjs.icustom.pojo.App;

/**
 * <h2>连接ambari数据库执行数据库脚本</h2>
 * @author flash
 *
 */
public class SQLScriptExecutor {

	private final static Logger logger = LoggerFactory.getLogger(SQLScriptExecutor.class);

	/**
	 * 数据库连接参数 driver,url,username,password
	 */
	private static final String CONN_PATH_PARAM = "?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true";

	private static Connection conn = null;
	private static PreparedStatement ps = null;
	private static ResultSet rs = null;
	// private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	// private static PreparedStatement ps = null;
	// private static ResultSet rs = null;

	public static void main(String[] args) throws Exception {

	}

	static public int execute(String sqlFile) throws Exception {
		return execute(sqlFile, null);
	}

	static public int execute(String sqlFile, App app) throws Exception {
		int result = 0;
		Connection connection = null;
		try {
			// 获取数据库连接
			connection = getConnection(app);
			//logger.info("\n59 connection:{}\n", connection);
			// 读取SQL脚本文件
			InputStream is = new FileInputStream(sqlFile);

			// 解析SQL脚本文件并执行
			String sql = IOUtils.toString(is, "UTF-8");
			QueryRunner runner = new QueryRunner();
			result = runner.update(connection, sql);
			//logger.info("\n67 runner.update result:{}\n", result);

			IOUtils.closeQuietly(is);
			return result;
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection(connection);
		}
	}

	/**
	 * 更新
	 * 
	 * @throws SQLException
	 */
	public static int exeUpdate(String sql, App app) throws SQLException {
		conn = getConnection(app);
		ps = conn.prepareStatement(sql);
		int result = ps.executeUpdate();

		return result;
	}

	/**
	 * 查询
	 * 
	 * @throws SQLException
	 */
	public static List<DatabaseSchema> exeQuery(String sql, App app) throws SQLException {
		conn = getConnection(app);
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
		List<DatabaseSchema> list = new ArrayList<DatabaseSchema>();
		if (rs.next()) {
			ResultSetMetaData rsmd = rs.getMetaData();
			
			DatabaseSchema db = null;
			while (rs.next()) {
				for (int _iterator = 0; _iterator < rsmd.getColumnCount(); _iterator++) {
					db = new DatabaseSchema();
					// getting the SQL column name
					String columnName = rsmd.getColumnName(_iterator + 1);
					// reading the value of the SQL column
					Object columnValue = rs.getObject(_iterator + 1);
					db.setColumnName(columnName);
					db.setColumnValue(columnValue);
					logger.info("\n{} :{}\n", columnName, columnValue);
					list.add(db);
				}
			}
		}
		return list;
	}

	private static Connection getConnection(App app) throws SQLException {
		String oldUrl = String.format("jdbc:mysql://%s:%s", app.getAppIp(),app.getPort());
		//logger.info("\noldUrl:{}\n", oldUrl);
		//logger.info("\nUsername:{}\nPassword:{}\n", app.getUsername(),app.getPassword());
		if(!app.getUsername().equals("root")) {
			oldUrl = oldUrl +"/"+ app.getUsername();
		}
		oldUrl = oldUrl + CONN_PATH_PARAM;
		logger.info("\noldUrl:{}\n", oldUrl);
		
		conn = DriverManager.getConnection(oldUrl, app.getUsername(), app.getPassword());
		return conn;
	}

	private static void closeConnection(Connection connection) throws SQLException {
		conn.close();
	}
}
